package com.rockontrol.ele.service.impl;

import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;

import com.rksp.util.DatabaseUtil;
import com.rksp.util.StringUtil;
import com.rockontrol.ele.pojo.Ba物资分类;
import com.rockontrol.ele.pojo.Ba知识库;
import com.rockontrol.ele.pojo.Ba编码映射;
import com.rockontrol.ele.pojo.Ba编码模式映射;
import com.rockontrol.ele.pojo.Ba规格型号;
import com.rockontrol.ele.pojo.Ma图纸;
import com.rockontrol.ele.pojo.Ma图纸分类;
import com.rockontrol.ele.pojo.Ma权限控制;
import com.rockontrol.ele.pojo.Ma物资流程审批;
import com.rockontrol.ele.pojo.Ma设备检修周期;
import com.rockontrol.ele.pojo.Ma设备预检修台帐;
import com.rockontrol.ele.service.MaBaseSvc;
import com.rockontrol.ele.service.model.dbtransaction.MaBaseTrans;
import com.rockontrol.ele.service.skeleton.MaBaseSvcSkeleton;
import com.rockontrol.ele.vo.BaAutoTypeVO;
import com.rockontrol.ele.vo.BaModelClassVO;
import com.rockontrol.ele.vo.DrawingCdt;
import com.rockontrol.ele.vo.EqCatMappingVo;
import com.rockontrol.ele.vo.KnowledgeOption;
import com.rockontrol.ele.vo.MaCategoryOption;
import com.rockontrol.ele.vo.MaCheckPointCdt;
import com.rockontrol.ele.vo.MaControlCdt;
import com.rockontrol.ele.vo.MaModelClassCdt;
import com.rockontrol.ele.vo.MaTypeOption;
import com.rockontrol.oup.pojo.Ba文档;

public class MaBaseSvcImpl extends MaBaseSvcSkeleton implements MaBaseSvc {
    private static Logger log = Logger.getLogger(MaBaseSvcImpl.class);
    private MaBaseTrans maBaseTrans;
    //编码映射服务
    public Boolean catMap(String srcId, String destId, String mappingId) {
        try {
            Ba编码模式映射 ba编码模式映射=getMapping(mappingId);
            Ba编码映射 ba编码映射=new Ba编码映射();
            ba编码映射.set分类(ba编码模式映射.get分类()); 
            ba编码映射.set源分类id(srcId);
            ba编码映射.set映射分类id(destId);
            ba编码映射.set模式映射id(mappingId);
            service.addObj(ba编码映射);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    public String createMapping(Ba编码模式映射 bm_) {
        try {
            service.addObj(bm_);
            return bm_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public String pageInitMappings(String type, String roll) {
        try {
            String sql = null;
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql="select convert(varchar(32), a.oid) as oid,a.映射名称,convert(varchar(32),a.源编码模式id) as 源编码模式id,convert(varchar(32),a.映射编码模式id) as 映射编码模式id,a.分类,a.备注,b.name as 源编码模式,c.name as 映射编码模式 from BA_编码模式映射 a ,BA_Dic b,BA_Dic c where a.源编码模式id=b.oid and a.映射编码模式id=c.oid and a.分类='"+type+"'";
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql="select to_char(a.OID) as \"oid\",a.映射名称,to_char(a.源编码模式id) as \"源编码模式id\",to_char(a.映射编码模式id) as \"映射编码模式id\",a.分类,a.备注,b.name as 源编码模式,c.name as 映射编码模式 from BA_编码模式映射 a ,BA_Dic b,BA_Dic c where a.源编码模式id=b.oid and a.映射编码模式id=c.oid and a.分类='"+type+"';";
            }
            return pageFind(roll, sql,new HashMap(),4,Ba编码模式映射.class);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public List<Ba编码模式映射> getMappings(String type) {
        try {
            String sql = null;
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql="select convert(varchar(32), a.oid) as oid,a.映射名称,convert(varchar(32),a.源编码模式id) as 源编码模式id,convert(varchar(32),a.映射编码模式id) as 映射编码模式id,a.分类,a.备注,b.name as 源编码模式,c.name as 映射编码模式 from BA_编码模式映射 a ,BA_Dic b,BA_Dic c where a.源编码模式id=b.oid and a.映射编码模式id=c.oid and a.分类='"+type+"'";
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql="select to_char( a.oid) as \"oid\",a.映射名称,to_char(a.源编码模式id) as \"源编码模式id\",to_char(a.映射编码模式id) as \"映射编码模式id\",a.分类,a.备注,b.name as 源编码模式,c.name as 映射编码模式 from BA_编码模式映射 a ,BA_Dic b,BA_Dic c where a.源编码模式id=b.oid and a.映射编码模式id=c.oid and a.分类='"+type+"';";
            }

            return service.findSQLAlias(sql, Ba编码模式映射.class);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public List<EqCatMappingVo> getMaCatMappings(String mappingId) {
        
        try {
            StringBuffer sql=new StringBuffer();
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql.append("select convert(varchar(32),map.mapid) as oid ,convert(varchar(32), a.oid) srcId,a.全称 as srcName,a.编码 as srcCode,convert(varchar(32), map.oid) destId,map.全称 as destName,map.编码 as destCode ");
                sql.append(" from BA_物资分类 a ");
                sql.append(" left join (select m.oid as mapid,源分类ID as srcid,b.* from BA_物资分类 b left join BA_编码映射 m on b.oid=m.映射分类ID where m.模式映射ID='").append(mappingId).append("') as map ");
                sql.append(" on a.oid=map.srcid ");
                sql.append("where a.编码模式=(select b.name as 源编码模式 from BA_编码模式映射 a left join BA_Dic b on a.源编码模式id=b.oid where a.oid='").append(mappingId).append("' )");
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql.append("select to_char(map.mapid) as \"oid\" ,to_char(a.oid) as \"srcId\",a.全称 as \"srcName\",a.编码 as \"srcCode\",to_char(map.oid) as \"destId\",map.全称 as \"destName\",map.编码 as \"destCode\" ");
                sql.append(" from BA_物资分类 a ");
                sql.append(" left join (select m.oid as \"mapid\",源分类ID as \"srcid\",b.* from BA_物资分类 b left join BA_编码映射 m on b.oid=m.映射分类ID where m.模式映射ID='").append(mappingId).append("') map ");
                sql.append(" on a.oid=map.srcid ");
                sql.append("where a.编码模式=(select b.name as 源编码模式 from BA_编码模式映射 a left join BA_Dic b on a.源编码模式id=b.oid where a.oid='").append(mappingId).append("' )");
            }
            return service.findSQLAlias(sql.toString(), EqCatMappingVo.class);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String pageInitGetMaCatMappings(String mappingId, String roll) {
        try {
            StringBuffer sql=new StringBuffer();
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
            }

            sql.append("select convert(varchar(32),map.mapid) as oid ,convert(varchar(32), a.oid) srcId,a.全称 as srcName,a.编码 as srcCode,convert(varchar(32), map.oid) destId,map.全称 as destName,map.编码 as destCode ");
            sql.append(" from BA_物资分类 a ");
            sql.append(" left join (select m.oid as mapid,源分类ID as srcid,b.* from BA_物资分类 b left join BA_编码映射 m on b.oid=m.映射分类ID where m.模式映射ID='").append(mappingId).append("') as map ");
            sql.append(" on a.oid=map.srcid ");
            sql.append("where a.编码模式=(select b.name as 源编码模式 from BA_编码模式映射 a left join BA_Dic b on a.源编码模式id=b.oid where a.oid='").append(mappingId).append("' )");
            return pageFind(roll,sql.toString(),new HashMap(),4, EqCatMappingVo.class);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public Ba编码模式映射 getMapping(String id) {
        try {
            String sql=null;
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql="select convert(varchar(32), a.oid) as oid,a.映射名称,convert(varchar(32),a.源编码模式id) as 源编码模式id,convert(varchar(32),a.映射编码模式id) as 映射编码模式id,a.分类,a.备注,b.name as 源编码模式,c.name as 映射编码模式 from BA_编码模式映射 a ,BA_Dic b,BA_Dic c where a.源编码模式id=b.oid and a.映射编码模式id=c.oid and a.oid='"+id+"'";
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql="select to_char(a.oid) as \"oid\",a.映射名称,to_char(a.源编码模式id) as \"源编码模式id\",to_char(a.映射编码模式id) as \"映射编码模式id\",a.分类,a.备注,b.name as 源编码模式,c.name as 映射编码模式 from BA_编码模式映射 a ,BA_Dic b,BA_Dic c where a.源编码模式id=b.oid and a.映射编码模式id=c.oid and a.oid='"+id+"';";
            }
            return (Ba编码模式映射) service.findSQLAlias(sql, Ba编码模式映射.class).get(0);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public Boolean updateMapping(Ba编码模式映射 bm) {
        try {
            service.modifyObj(bm);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    public Boolean deleteMap(String oid) {
        try {
            service.removeObj(Ba编码映射.class,oid);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    public Boolean deleteMapping(String id) {
        try {
            service.removeObj(Ba编码模式映射.class,id);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }
    
  //物资分类
    public List<BaModelClassVO> getMaModelClass(MaModelClassCdt cdt) {
        try {
            List<BaModelClassVO> modelList=null;
            StringBuffer sql1 = new StringBuffer();
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql1.append("select convert(varchar(32),a.oid) oid,a.名称 name,a.全称 fullName,a.编码 code,'分类' type,convert(varchar(32),a.父节点id) pid from BA_物资分类 a where 1=1");
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql1.append("select to_char(a.oid) \"oid\",a.名称 \"name\",a.全称 \"fullName\",a.编码 \"code\",cast('分类' as varchar2(8)) \"type\",to_char(a.父节点id) as \"pid\" from BA_物资分类 a where 1=1");
            }

            if(cdt!=null){

                if(!StringUtil.isEmpty(cdt.getPid())){
                	if("null".equals(cdt.getPid())) {
                    	sql1.append(" and a.父节点ID is null ");
                    }else{
                    	sql1.append(" and a.父节点ID = '").append(cdt.getPid()).append("'");
                    }
                }
                if(!StringUtil.isEmpty(cdt.getName())){
                    if(cdt.getName().indexOf(",")!=-1){
                        String[] names=cdt.getName().split(",");
                        sql1.append(" and (");
                        for(int i=0;i<names.length;i++){
                            if(i>0)
                                sql1.append(" or ");
                            sql1.append(" a.名称 = '").append(names[i]).append("'");
                        }
                        sql1.append(" )");
                    }else
                        sql1.append(" and a.名称 = '").append(cdt.getName()).append("'");
                }
            
            }
            if(cdt==null){
                sql1.append(" and a.父节点id is null");
            }else if(!StringUtil.isEmpty(cdt.getPid())){
                String sql2=null;
                if("SQLSERVER".equals(dataBaseType)){
                        //数据库为sqlserver时执行的操作
                    sql2="select convert(varchar(32),oid) oid,型号 name,流水号 code,'型号' type,'"+cdt.getPid()+"' pid,货品名称 货品名称 from BA_规格型号 where 物资分类ID='"+cdt.getPid()+"' ";
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                    sql2="select to_char(oid) \"oid\",型号 \"name\",流水号 \"code\",cast('型号' as varchar2(8)) \"type\",cast('"+cdt.getPid()+"' as varchar2(32)) \"pid\",货品名称 货品名称  from BA_规格型号  where 物资分类ID='"+cdt.getPid()+"' ";
                }
                modelList=service.findSQLAlias(sql2.toString(), BaModelClassVO.class);
            }
            sql1.append(" order by a.编码 ");
            List<BaModelClassVO> typeList=service.findSQLAlias(sql1.toString(),BaModelClassVO.class);
            if(modelList!=null)
                typeList.addAll(modelList);
            return typeList;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String createMaCategory(Ba物资分类 ec_) {
        try {
            service.addObj(ec_);
            return ec_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public Ba物资分类 getMaCategory(String id_) {
        try {
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            String sql= null;
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql="select convert(varchar(32),a.oid) as oid ,convert(varchar(32),a.父节点id) as 父节点id,b.名称 as 父节点,a.所属设备,a.编码模式,a.单位,a.编码,a.名称,a.全称,a.备注,a.采购权限 from BA_物资分类 a left join BA_物资分类 b on a.父节点id=b.oid where a.oid='"+id_+"'";
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql="select to_char(a.oid) as \"oid\" ,to_char(a.父节点id) as \"父节点id\",b.名称 as 父节点,cast(a.所属设备 as varchar2(32)) as 所属设备,a.编码模式,a.单位,a.编码,a.名称,a.全称,a.备注,a.采购权限 from BA_物资分类 a left join BA_物资分类 b on a.父节点id=b.oid where a.oid='"+id_+"'";
            }
            return (Ba物资分类) service.findSQLAlias(sql,Ba物资分类.class).get(0);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public List<Ba物资分类> getMaCategorys(MaCategoryOption op_) {
        try{
            return service.findObjsHQL(getMaCategorysSql(op_));
        }catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String pageInitGetMaCategorys(MaCategoryOption op_, String roll) {
        try {
            return pageFind(roll, getMaCategorysSql(op_));
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String getMaCategorysSql(MaCategoryOption op_){
        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
        StringBuffer hql=new StringBuffer();
        hql.append("from Ba物资分类  a where 1=1 ");
        if(null!=op_){
            if(op_.getType()){
                hql.append(" a.父节点id is null");
            }else{
                if(!StringUtil.isEmpty(op_.getName())){
                    hql.append(" and ( a.名称 like '%"+op_.getName().trim()+"%'  or a.全称 like '%"+op_.getName().trim()+"%' ) ");
                }
                if(!StringUtil.isEmpty(op_.getParentId())){
                    if("SQLSERVER".equals(dataBaseType)){
                        //数据库为sqlserver时执行的操作
                        hql.append(" and a.编码 like (select 编码 from Ba物资分类 where oid = '").append(op_.getParentId()).append("')+'%'");
                    }else if("ORACLE".equals(dataBaseType)){
                            //数据库为oracle时执行的操作
                        hql.append(" and a.编码 like (select 编码 from Ba物资分类 where oid = '").append(op_.getParentId()).append("')||'%'");
                    }
                }
                if(!StringUtil.isEmpty(op_.getRoot())){
                    if(op_.getRoot().indexOf(",")!=-1){
                        String[] names=op_.getRoot().split(",");
                        hql.append(" and (");
                        for(int i=0;i<names.length;i++){
                            if(i>0){
                                hql.append(" or ");
                            }
                         if("SQLSERVER".equals(dataBaseType)){
                               //数据库为sqlserver时执行的操作
                                hql.append(" a.编码 like (select 编码 from Ba物资分类 where 名称 = '").append(names[i]).append("')+'%'");
                         }else if("ORACLE".equals(dataBaseType)){
                                //数据库为oracle时执行的操作
                                hql.append(" a.编码 like (select 编码 from Ba物资分类 where 名称 = '").append(names[i]).append("')||'%'");
                         }
                            
                        }
                        hql.append(" )");
                    }else
                        if("SQLSERVER".equals(dataBaseType)){
                            //数据库为sqlserver时执行的操作
                            hql.append(" and a.编码 like (select 编码 from Ba物资分类 where 名称 = '").append(op_.getRoot()).append("')+'%'");
                        }else if("ORACLE".equals(dataBaseType)){
                                //数据库为oracle时执行的操作
                            hql.append(" and a.编码 like (select 编码 from Ba物资分类 where 名称 = '").append(op_.getRoot()).append("')||'%'");
                        }
                }
                if(!StringUtil.isEmpty(op_.getAuth())){
                    hql.append(" and a.采购权限 = '").append(op_.getParentId()).append("'");
                }
            }
        }
        return hql.toString();
    }

    public Boolean checkMaCategoryCode(String code){
        try {
            List list=service.findObjsHQL("from Ba物资分类  a where a.编码='"+code+"'");
            return list==null||list.isEmpty();
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }
    
    public Boolean updateMaCategory(Ba物资分类 ec_) {
        try {
            service.modifyObj(ec_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage());
            return false;
        }
    }

    public Boolean deleteMaCategory(String id_) {
        try {
            service.removeObj(Ba物资分类.class,id_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    //规格型号
    public String createMaType(Ba规格型号 et_) {
        try {
            service.addObj(et_);
            return et_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public Ba规格型号 getMaType(String id_) {
        try{
            return (Ba规格型号) service.findObj(Ba规格型号.class, id_);
        }catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public List<Ba规格型号> getMaTypes(MaTypeOption op_) {
        try {
            return service.findObjsHQL(getMaTypesSql(op_));
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String pageInitGetMaTypes(MaTypeOption op_, String roll) {
        try {
            return pageFind(roll, getMaTypesSql(op_));
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String getMaTypesSql(MaTypeOption op_){
        StringBuffer hql=new StringBuffer();
        hql.append("from Ba规格型号 a where 1=1 ");
        if(null!=op_){
            if(!StringUtil.isEmpty(op_.getName())){
                hql.append(" and a.货品名称||a.型号 like '%"+op_.getName().trim()+"%' ");
            }
            if(!StringUtil.isEmpty(op_.getCategory())){
                if(op_.getCategory().equals("物资采供部")){
                	hql.append(" and a.物资编号 like '01%' ");
                }else{
                	hql.append(" and a.物资编号 like '04%' ");
                }
            }
            
        }
        return hql.toString();
    }
    
    public Boolean updateMaType(Ba规格型号 et_) {
        try {
            service.modifyObj(et_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage());
            return false;
        }
    }

    public Boolean deleteMaType(String id_) {
        try {
            service.execSQL("delete BA_规格型号 where OID='"+id_+"'");
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    //知识库服务
    public String createKnowledge(Ba知识库 kn_) {
        try {
            //默认为文档类型
//            kn_.set节点类型(2L);
            service.addObj(kn_);
            return kn_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public Ba知识库 getKnowledge(String id_) {
        try{
            return (Ba知识库) service.findObj(Ba知识库.class, id_);
        }catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public List<Ba知识库> getKnowledges(KnowledgeOption opt) {
        try {
            return service.findObjsHQL(getKnowledgesSql(opt));
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public String pageIintGetKnowledges(KnowledgeOption opt,String roll) {
        try {
            return pageFind(roll, getKnowledgesSql(opt));
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    private String getKnowledgesSql(KnowledgeOption opt){
        StringBuffer sql=new StringBuffer();
        sql.append("from Ba知识库 a where 1=1");
        if(opt!=null){
            if(!StringUtil.isEmpty(opt.getTitle())){
                sql.append(" and a.节点名称 ");
                if(opt.isCheck())
                    sql.append("= '").append(opt.getTitle().trim()).append("'");
                else 
                    sql.append("like '%").append(opt.getTitle().trim()).append("%'");
            }
            if(!StringUtil.isEmpty(opt.getKeyWord())&&!opt.isCheck()){
                sql.append(" and ( a.节点名称 like '%"+opt.getKeyWord().trim()+"%' or a.节点内容 like '%"+opt.getKeyWord().trim()+"%')");
            }
            if(!StringUtil.isEmpty(opt.getPid())){
                sql.append(" and 父节点id= '").append(opt.getPid().trim()).append("' ");
            }
            if(!StringUtil.isEmpty(opt.getType())){
                sql.append(" and 节点类型 like '%").append(Long.parseLong(opt.getType())).append("%' ");
            }
        }
        return sql.toString();
        
    }
    
    public Boolean updateKnowledge(Ba知识库 kn_) {
        try {
            service.modifyObj(kn_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage());
            return false;
        }
    }

    public Boolean deleteKnowledge(String id_) {
        try {
            service.removeObj(Ba知识库.class, id_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage());
            return false;
        }
    }

    //审批服务
    public String createTheMaterialManage(Ma物资流程审批 mpm_) {
        try {
            service.addObj(mpm_);
            return mpm_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public List<Ma物资流程审批> getMaterialManages(String id_) {
        try {
            StringBuffer hql=new StringBuffer();
            hql.append("from Ma物资流程审批 a where a.业务id='").append(id_).append("'");
            return service.findObjsHQL(hql.toString());
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public Ma物资流程审批 getTheMaterialManage(String id_) {
        try {
            return (Ma物资流程审批) service.findObj(Ma物资流程审批.class, id_);
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public Boolean updateMaterialManages(String id_) {
        //TODO 该方法无用
        return null;
    }

    //分页服务
    public String pageNumMaBase(String roll, Integer num) {
        try {
            return super.pageNum(roll, num);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    public List<BaAutoTypeVO> getMaAutoType(String type,String model) {
        try {
            StringBuffer sql=new StringBuffer();
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql.append("select convert(varchar(32),a.OID) 分类id,a.编码,a.采购权限,b.单位,a.所属设备,a.名称,a.全称,convert(varchar(32),b.OID) 型号id,b.型号,b.厂商名称,convert(varchar(32),b.生产厂商ID) 生产厂商id,b.流水号,b.单价,b.备注,b.分类名称,b.货品名称,c.库存量 as 库存量,单价 as 均价"); 
                sql.append(" from BA_物资分类 a right join BA_规格型号 b on a.OID=b.物资分类ID left join MA_物资库存 c on b.OID=c.规格型号ID");
                sql.append(" WHERE 1=1 ");
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql.append("select to_char(a.OID) \"分类id\",a.编码,a.采购权限,b.单位,cast(a.所属设备 as varchar2(32)) as 所属设备,a.名称,a.全称,to_char(b.OID) \"型号id\",b.型号,b.厂商名称,to_char(b.生产厂商ID) \"生产厂商id\",b.流水号,b.单价,b.备注,b.货品名称,c.库存量 as 库存量,单价 as 均价"); 
                sql.append(" from BA_物资分类 a right join BA_规格型号 b on a.OID=b.物资分类ID left join MA_物资库存 c on b.OID=c.规格型号ID");
                sql.append(" WHERE 1=1 ");
            }

            if(!StringUtil.isEmpty(type))
                if("设备".equals(type)){
                    sql.append(" and (a.编码 like '01%' or a.编码 like '02%')");
                }else{
                    if("SQLSERVER".equals(dataBaseType)){
                        //数据库为sqlserver时执行的操作
                        sql.append(" and a.编码 like (select 编码 from BA_物资分类 where 全称='").append(type).append("')+'%'");
                    }else if("ORACLE".equals(dataBaseType)){
                            //数据库为oracle时执行的操作
                        sql.append(" and a.编码 like (select 编码 from BA_物资分类 where 全称='").append(type).append("')||'%'");
                    }
                }
            if(!StringUtil.isEmpty(model)){
            	if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
            		sql.append(" and (b.oid='").append(model).append("' or a.编码+b.流水号 like '%").append(model).append("%' or a.名称 like '%").append(model).append("%' or a.全称 like '%").append(model).append("%' or b.型号 like '%").append(model).append("%' or b.分类名称 like '%").append(model).append("%' or b.货品名称 like '%").append(model).append("%')");
                }else if("ORACLE".equals(dataBaseType)){
                        //数据库为oracle时执行的操作
                	sql.append(" and (b.oid='").append(model).append("' or a.编码||b.流水号 like '%").append(model).append("%' or a.名称 like '%").append(model).append("%' or a.全称 like '%").append(model).append("%' or b.型号 like '%").append(model).append("%' or b.分类名称 like '%").append(model).append("%' or b.货品名称 like '%").append(model).append("%')");
                }
            	
            }
                
            if("其它".equals(model)){
                sql.append(" and a.名称 = '"+model+"'");
            }
            return service.findSQLAlias(sql.toString(), BaAutoTypeVO.class);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
    }
    
    public List<BaAutoTypeVO> getElAutoType(String type, String model){
        try {
            StringBuffer sql=new StringBuffer();
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            if("SQLSERVER".equals(dataBaseType)){
	                //数据库为sqlserver时执行的操作
	            sql.append("select convert(varchar(32),a.OID) 分类id,a.编码,a.采购权限,b.单位,a.所属设备,a.名称,a.全称,convert(varchar(32),b.OID) 型号id,b.型号,b.厂商名称,convert(varchar(32),b.生产厂商ID) 生产厂商id,b.流水号,b.单价,b.备注,b.分类名称,b.货品名称"); 
	            sql.append(" from BA_物资分类 a right join BA_规格型号 b on a.OID=b.物资分类ID");
	            sql.append(" WHERE 1=1 ");
	        }else if("ORACLE".equals(dataBaseType)){
	                //数据库为oracle时执行的操作
	            sql.append("select to_char(a.OID) \"分类id\",a.编码,a.采购权限,b.单位,cast(a.所属设备 as varchar2(32)) as 所属设备,a.名称,a.全称,to_char(b.OID) \"型号id\",b.型号,b.厂商名称,to_char(b.生产厂商ID) \"生产厂商id\",b.流水号,b.单价,b.备注,b.货品名称"); 
	            sql.append(" from BA_物资分类 a right join BA_规格型号 b on a.OID=b.物资分类ID");
	            sql.append(" WHERE 1=1 ");
	        }
            if(!StringUtil.isEmpty(type))
                if("设备".equals(type)){
                    sql.append(" and (a.编码 like '01%')");
                }else{
                    if("SQLSERVER".equals(dataBaseType)){
                        //数据库为sqlserver时执行的操作
                        sql.append(" and a.编码 like (select 编码 from BA_物资分类 where 全称='").append(type).append("')+'%'");
                    }else if("ORACLE".equals(dataBaseType)){
                            //数据库为oracle时执行的操作
                        sql.append(" and a.编码 like (select 编码 from BA_物资分类 where 全称='").append(type).append("')||'%'");
                    }
                }
            if(!StringUtil.isEmpty(model))
                sql.append(" and (a.编码+b.流水号 like '%").append(model).append("%' or a.名称 like '%").append(model).append("%' or a.全称 like '%").append(model).append("%' or b.型号 like '%").append(model).append("%'").append(" or b.分类名称 like '%").append(model).append("%')");
            if("其它".equals(model)){
                sql.append(" and b.货品名称 = '"+model+"'");
            }
            return service.findSQLAlias(sql.toString(), BaAutoTypeVO.class);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    public String createTheAuthorityControl(Ma权限控制 mpm){
        try{
            service.addObj(mpm);
            return mpm.getOid();
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
            throw new RuntimeException();
        }
        
    }

    public boolean deleteTheAuthorityControl(String oid){
        try{
            service.removeObj(Ma权限控制.class, oid);
            return true;
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
            return false;
        }
        
        
    }
    public String getAuthorityControlsSql(MaControlCdt cdt){
        StringBuffer sqlBuffer=new StringBuffer("from Ma权限控制 a  where 1=1 ");
        if(cdt!=null){
            if(!StringUtil.isEmpty(cdt.get模块id())){
                sqlBuffer.append("and a.模块id='"+cdt.get模块id()+"'");
            }
            if(!StringUtil.isEmpty(cdt.get模块名称())){
                sqlBuffer.append("and a.模块名称 like '%"+cdt.get模块名称()+"%'");
            }
            if(!StringUtil.isEmpty(cdt.get系统id())){
                sqlBuffer.append("and a.系统id = '"+cdt.get系统id()+"'");
            }
            if(!StringUtil.isEmpty(cdt.get角色id())){
                sqlBuffer.append("and a.角色id = '"+cdt.get角色id()+"'");
            }
            if(!StringUtil.isEmpty(cdt.get系统名称())){
                sqlBuffer.append("and a.系统名称 like '%"+cdt.get系统名称()+"%'");
            }
            if(!StringUtil.isEmpty(cdt.get限制字段())){
                sqlBuffer.append("and a.限制字段 like '%"+cdt.get限制字段()+"%'");
            }
        }
        return sqlBuffer.toString();
    }
    public List<Ma权限控制> getAuthorityControls(MaControlCdt cdt){
         try{
             return service.findObjsHQL(getAuthorityControlsSql(cdt));
         }catch(Exception e){
             log.error(e.getMessage());
             e.printStackTrace();
             return null;
         }
    }

    public Ma权限控制 getTheAuthorityControl(String oid){
        return (Ma权限控制)service.findObj(Ma权限控制.class, oid);
    }

    public boolean updateTheAuthorityControl(Ma权限控制 mpm){
        try{
            service.modifyObj(mpm);
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
        
    }
    
    public String pageInitGetAuthorityControls(MaControlCdt cdt, String roll){
        try{
            return pageFind(roll, getAuthorityControlsSql(cdt));
        }catch(Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
       
    }

    public String createTheCheckPoint(Ma设备检修周期 ck_) {
        try {
            service.addObj(ck_);
            return ck_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public Ma设备检修周期 getTheCheckPoint(String id_) {
        try {
            return (Ma设备检修周期) service.findObj(Ma设备检修周期.class, id_);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public List<Ma设备检修周期> getTheCheckPoints(MaCheckPointCdt cdt) {
        try {
            return service.findObjsHQL(getTheCheckPointsSql(cdt));
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String pageInitgetTheCheckPoints(MaCheckPointCdt cdt, String roll) {
        try {
            return pageFind(roll, getTheCheckPointsSql(cdt));
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    private String getTheCheckPointsSql(MaCheckPointCdt cdt){
        StringBuffer sql=new StringBuffer();
        sql.append("from Ma设备检修周期 a where 1=1 ");
        if(cdt!=null){
            if(!StringUtil.isEmpty(cdt.get设备分类())){
                sql.append(" and a.设备分类 = '").append(cdt.get设备分类()).append("' ");
            }
            if(!StringUtil.isEmpty(cdt.get设备分类id())){
                sql.append(" and a.设备分类id = '").append(cdt.get设备分类id()).append("' ");
            }
            if(!StringUtil.isEmpty(cdt.get设备分类编码())){
                sql.append(" and a.设备分类编码 = '").append(cdt.get设备分类编码()).append("' ");
            }
            if(!StringUtil.isEmpty(cdt.get设备型号())){
                sql.append(" and a.设备型号 = '").append(cdt.get设备型号()).append("' ");
            }
            if(!StringUtil.isEmpty(cdt.get设备型号id())){
                sql.append(" and a.设备型号id = '").append(cdt.get设备型号id()).append("' ");
            }
            if(!StringUtil.isEmpty(cdt.getType())){
                sql.append(" and a.检修类型 = '").append(cdt.getType()).append("' ");
            }
        }
        return sql.toString();
    }

    public Boolean updateTheCheckPoint(Ma设备检修周期 ck_) {
        try {
            service.modifyObj(ck_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    public Boolean deleteTheCheckPoint(String id_) {
        try {
            service.removeObj(Ma设备检修周期.class,id_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    public void execCheckPoint() {
        try {
            maBaseTrans.serviceExecCheckPoint();
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
    }

    public Ma设备预检修台帐 getTheCheckAccout(String id, String eqId) {
        try {
            Ma设备预检修台帐 acc=null;
            if(!StringUtil.isEmpty(id))
                acc= (Ma设备预检修台帐) service.findObj(Ma设备预检修台帐.class, id);
            else if(!StringUtil.isEmpty(eqId)){
                List<Ma设备预检修台帐> list=service.findObjsHQL("from Ma设备预检修台帐 a where a.设备id='"+eqId+"'");
                acc= list!=null&&!list.isEmpty()?list.get(0):null;
            }
            if(acc!=null)
                acc.setCk((Ma设备检修周期) service.findObj(Ma设备检修周期.class, acc.get检修周期id()));
            return acc;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String pageInitgetTheCheckAccouts(MaCheckPointCdt cdt, String roll) {
        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
        try {
            StringBuffer sql=new StringBuffer();
            sql.append("from Ma设备预检修台帐 a where a.状态='有效' ");
            if(cdt!=null){
                if(!StringUtil.isEmpty(cdt.get设备分类())){
                    sql.append(" and a.设备分类 = '").append(cdt.get设备分类()).append("' ");
                }
                if(!StringUtil.isEmpty(cdt.get设备分类id())){
                    sql.append(" and a.设备分类id = '").append(cdt.get设备分类id()).append("' ");
                }
                if(!StringUtil.isEmpty(cdt.get设备分类编码())){
                    sql.append(" and a.设备分类编码 = '").append(cdt.get设备分类编码()).append("' ");
                }
                if(!StringUtil.isEmpty(cdt.get设备型号())){
                    sql.append(" and a.设备型号 = '").append(cdt.get设备型号()).append("' ");
                }
                if(!StringUtil.isEmpty(cdt.get设备型号id())){
                    sql.append(" and a.设备型号id = '").append(cdt.get设备型号id()).append("' ");
                }
                if(!StringUtil.isEmpty(cdt.get设备id())){
                    sql.append(" and a.设备id = '").append(cdt.get设备型号id()).append("' ");
                }
                if(!StringUtil.isEmpty(cdt.getType())){
                    sql.append(" and a.检修类型 = '").append(cdt.getType()).append("' ");
                }
                if(cdt.get剩余时间()!=null){
                    if("SQLSERVER".equals(dataBaseType)){
                        //数据库为sqlserver时执行的操作
                        sql.append(" and a.剩余时间 <= ").append(cdt.getType()).append(" ");
                    }else if("ORACLE".equals(dataBaseType)){
                            //数据库为oracle时执行的操作
                        sql.append(" and a.剩余时间 <= to_date('").append(cdt.getType()).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
                    }
                }
            }
            return pageFind(roll, sql.toString());
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }
    
    public Boolean updateTheCheckAccount(Ma设备预检修台帐 ck_) {
        try {
            service.modifyObj(ck_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    public Boolean deleteTheCheckAccount(String id_) {
        try {
            service.removeObj(Ma设备预检修台帐.class,id_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return false;
        }
    }

    public MaBaseTrans getMaBaseTrans() {
        return maBaseTrans;
    }

    public void setMaBaseTrans(MaBaseTrans maBaseTrans) {
        this.maBaseTrans = maBaseTrans;
    }

    public String createEQCode(String acronym, String model, String modelid){
        String eqCode;
        DecimalFormat format;
        if(model != ""){
            if(modelid !=""){
                try {
                    if(modelid.lastIndexOf("-") >=999)
                         format = new DecimalFormat("0000");  
                    else
                         format = new DecimalFormat("000");
                    int num = Integer.parseInt(modelid.substring(modelid
                            .lastIndexOf("-") + 1, modelid.length()));
                    eqCode="TRQ" + acronym.trim() + "-" + model.trim() + "-" + format.format(num + 1);
                } catch (Exception e) {
                    eqCode = "TRQ" + acronym.trim() + "-" + model.trim() + "-001";
                }
            }else{
                eqCode = "TRQ" + acronym.trim() + "-" + model.trim()  + "-001";
            }
        }else{
            if(modelid !=""){
                try {
                    if(modelid.lastIndexOf("-") >=999)
                         format = new DecimalFormat("0000");  
                    else
                         format = new DecimalFormat("00");
                    int num = Integer.parseInt(modelid.substring(modelid
                            .lastIndexOf("-") + 1, modelid.length()));
                    eqCode="TRQ" + acronym.trim() + "-" + format.format(num + 1);
                } catch (Exception e) {
                    eqCode = "TRQ" + acronym.trim() +  "-001";
                }
            }else{
                eqCode = "TRQ" + acronym.trim()   + "-001";
            } 
        }
        return eqCode;
    }

    public String createTheDrawing(Ma图纸 el_){
        try {
            service.addObj(el_);
            return el_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public String createTheDrawingType(Ma图纸分类 el_){
        try {
            service.addObj(el_);
            return el_.getOid();
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public Boolean deleteTheDrawing(String id_){
        Boolean flag = false;
        try {
            service.execSQL(" delete from [BA_文档] where [OID]='" + id_ + "' ");         
            flag = true;
        } catch (Exception e) {
            log.error(e.getMessage());
            throw new RuntimeException();
        }
        return flag;
    }

    public Boolean deleteTheDrawingType(String id_){
        Boolean flag = false;
        try {
            service.execSQL(" delete from MA_图纸分类 where OID='" + id_ + "' ");
            StringBuffer sql=new StringBuffer();
            sql.append("from Ma图纸  where 分类id='"+id_+"'");
            List<Ma图纸> list = service.findObjsHQL(sql.toString());
            service.execSQL(" delete from MA_图纸 where 分类ID='" + id_ + "' ");
            service.execSQL(" delete from BA_文档 where 业务ID='" + list.get(0).getOid() + "' ");         
            flag = true;
        } catch (Exception e) {
            log.error(e.getMessage());
            throw new RuntimeException();
        }
        return flag;
    }

    public Ma图纸 getTheDrawing(String id_){
        try{
            Ma图纸 drawing  = (Ma图纸) service.findObj(Ma图纸.class, id_);
            if(drawing != null && !drawing.equals("")){
                List<Ba文档> fileList;
                fileList = service.findObjsHQL("from Ba文档 where 业务id='"
                        + drawing.getOid() + "'");
                drawing.setDrawingList(fileList);
            }
            return drawing;
        }catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public Ma图纸分类 getTheDrawingType(String id_){
        try{
            return (Ma图纸分类) service.findObj(Ma图纸分类.class, id_);
        }catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public List<Ma图纸分类> getTheDrawingTypes(DrawingCdt cdt){
        try {
            String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
            StringBuffer sql1=new StringBuffer();
            if("SQLSERVER".equals(dataBaseType)){
                    //数据库为sqlserver时执行的操作
                sql1.append("select convert(varchar(32),a.oid) oid,a.名称 名称,a.编码 as 编码,a.备注 as 备注,convert(varchar(32),a.父节点id) 父节点id from MA_图纸分类 a where 1=1");
            }else if("ORACLE".equals(dataBaseType)){
                    //数据库为oracle时执行的操作
                sql1.append("select to_char(a.oid) \"oid\",a.名称 名称,a.编码 as 编码,a.备注 as 备注,to_char(a.父节点id) \"父节点id\" from MA_图纸分类 a where 1=1");
            }
            if(cdt!=null){
                if(!StringUtil.isEmpty(cdt.getPid()))
                    sql1.append(" and a.父节点ID = '").append(cdt.getPid()).append("'");
                if(!StringUtil.isEmpty(cdt.get名称())){
                    sql1.append(" and a.名称 = '").append(cdt.get名称()).append("'");
                }
            }
            List<Ma图纸分类> typeList=service.findSQLAlias(sql1.toString(),Ma图纸分类.class);
            return typeList;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    public String pageInitgetTheDrawing(DrawingCdt cdt, String roll){
        try{
            StringBuffer sql=new StringBuffer();
            sql.append("from Ba文档 a  where a.表名='EL_图纸分类' and 1=1");
            if(cdt != null){
                if(!StringUtil.isEmpty(cdt.getPid())){
                    sql.append(" and a.业务id = '"+cdt.getPid()+"'");
                }
                if(!StringUtil.isEmpty(cdt.get名称())){
                    sql.append(" and a.上传文件名 = '"+cdt.get名称()+"'");
                }
            }
            return pageFind(roll, sql.toString());
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }
    
    public Boolean updateTheDrawing(Ma图纸 el_){
        try {
            service.modifyObj(el_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage());
            return false;
        }
    }

    public Boolean updateTheDrawingType(Ma图纸分类 el_){
        try {
            service.modifyObj(el_);
            return true;
        } catch (Exception e) {
            log.error(e.getMessage());
            return false;
        }
    }
    //2013-02-04 杨晶 增加拖拽修改功能
    //pid拖拽后父节点id，code之前父节点的编码，tid被拖拽的节点id，type被拖拽节点类型
    public Boolean updateDropTypes(String pid,String code,String tid,String type){
    	//TODO 如果type为分类将tid的父节点id修改为pid，同时修改节点下所有分类编码。如果type为型号则修改型号所属的分类。
    	 try {
             return maBaseTrans.serviceUpdateDropTypes(pid,code, tid, type);
         } catch (Exception e) {
        	 e.printStackTrace();
             log.error(e.getMessage());
             return false;
         }
    }

	public String getCode(String id_) {
		String code = null;
		List<Ba规格型号> codeList=service.findSQLAlias("select max(a.流水号) as 流水号 from BA_规格型号  a where a.物资分类ID='"+id_+"'",Ba规格型号.class);
		if(codeList != null & codeList.size()>0){
			code=codeList.get(0).get流水号();
		}else{
			code="000001";
		}
		return code;
	}
	
}
